Re: Arrays, placeholders, and column types

At 2:37 PM -0400 10/25/04, Tom Lane wrote:
>Dan Sugalski <dan@sidhe.org> writes:
>>  I've got some SQL statements that I'm issuing from my app using the
>>  PQexecParams() C call. All the parameters are passed in as literal
>>  string parameters (that is, the paramTypes array entry for each
>>  placeholder is set to 0) letting the engine convert.
>
>>      INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3, $4, $5])
>>     DB error is: ERROR:  column "xyzzy" is of type numeric[] but
>>  expression is of type text[]
>
>The ARRAY[] construct forces determination of the array type, and it
>defaults to text[] in the absence of any type information from the array
>components.  (There's been some discussion of allowing the array type
>determination to be postponed further, but we haven't thought of a good
>way to do it yet.)  What you'll need to do is specify at least one of
>the array elements to be "numeric", either via paramTypes or with a cast
>in the SQL command:
>
>INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3::numeric, $4, $5])

Hrm. Okay, not a problem. (I was assuming the column type would be
used to type the array, though I can see reasons to not do so) Is
there any particular speed advantage to casting over setting
paramTypes, or vice versa?
--
                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
dan@sidhe.org                         have teddy bears and even
                                       teddy bears get drunk